<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2017/12/27
 * Time: 10:24
 */

class Goods_sku_model extends CI_Model
{

    public $table_name = 'goods_sku';
    public $limit_rows = 15;

    public function sku_list($param, $is_all = 0)
    {

        $this->db->select('gs.*,g.goods_id,ss.sl_title,g.goods_name,g.supp_url,g.is_delete,g.is_on_sale,g.add_time,g.updated_time,g.cn_name,g.original_img,g.in_price,g.shop_price,g.goods_weight,g.goods_sn,c.cate_name');
        $this->db->from('goods_sku AS gs');
        $this->db->join('shelves_sku as ss', 'ss.sku_id = gs.sku_id and ss.is_del = 0', 'left');
        $this->db->join('goods AS g', 'gs.goods_id = g.goods_id', 'left');
        $this->db->join('category AS c', 'g.cat_id = c.cate_id', 'left');

        if ($param['sku_name']) {
            $sku_num_sn = preg_replace("/[a-zA-Z]+/i", '', trim($param['sku_name']));
            $this->db->where("gs.sku_num_sn LIKE '%{$sku_num_sn}%'");
        }

        if ($param['goods_id']) {
            $this->db->where("g.goods_id = '{$param['goods_id']}'");
        }

        if ($param['sku_ids']) {
            $this->db->where_in('gs.sku_id', $param['sku_ids']);
        }

        if ($param['goods_sn']) {
            $this->db->where("g.goods_sn = '{$param['goods_sn']}'");
        }

        if ($param['sl_title']) {
            $this->db->where("ss.sl_title = '{$param['sl_title']}'");
        }

        if ($param['group_by']) {
            $this->db->group_by($param['group_by']);
        }

        if ($param['order_by']) {
            $this->db->order_by($param['order_by']);
        } else {
            $this->db->order_by('gs.goods_id DESC');
        }

        if ($is_all == 0) {
            $this->db->limit($this->limit_rows,$this->limit_rows * $param['page']);
        }

        $query = $this->db->get();
        return $query->result_array();
    }


    public function seek_goods_sn($sku_num_sn)
    {

        $this->db->from('goods_sku');

        foreach ($sku_num_sn as $val) {
            $this->db->or_where("sku_num_sn = '{$val}'");
        }

        $this->db->select('goods_id');
        $this->db->group_by('goods_id');
        $query = $this->db->get();

        $goods_ids = array();
        foreach ($query->result_array() as $row) {
            $goods_ids[] = $row['goods_id'];
        }

        return $goods_ids;
    }

    public function sku_num($param)
    {
        $this->db->from('goods_sku as gs');
        $this->db->join('shelves_sku as ss', 'ss.sku_id = gs.sku_id and ss.is_del = 0', 'left');
        $this->db->join('goods AS g', 'gs.goods_id = g.goods_id', 'left');
        $this->db->where('gs.is_delete', 0);

        if ($param['sku_name']) {
            $sku_num_sn = preg_replace("/[a-zA-Z]+/i", '', $param['sku_name']);
            $this->db->where("gs.sku_num_sn LIKE '%{$sku_num_sn}%'");
        }

        if ($param['sl_title']) {
            $this->db->where("ss.sl_title = '{$param['sl_title']}'");
        }

        return $this->db->count_all_results();
    }

    public function sku_info($param, $join = 'left')
    {
        $this->db->from('goods_sku AS gs');
        $this->db->select('gs.*, g.goods_sn');
        $this->db->join('goods AS g', 'gs.goods_id = g.goods_id', $join);
        $this->db->where($param);



        $query = $this->db->get();
        return $query->row_array();
    }

    public function goods_fp()
    {
        $query = $this->db->from('goods_sku')->where("sku_number > 0")->get();

        $goods_sku = $query->result_array();

        return $goods_sku;
    }

    public function up_sku_info($data, $sku_sn)
    {

        $sl_title = $data['sl_title'];
        $sku_number = $data['sku_number'];

        if ($sku_sn) {
           // $this->db->set('sku_number', "sku_number + {$data['sku_number']}", FALSE);
           // $this->db->where("sku_id = '{$sku_sn}'");
           // $this->db->update('goods_sku');

            print_r($data);

            if ($data['sl_id']) {
                $shelves_sku_data = array(
                    'sku_id' => $sku_sn,
                    'sl_id' => $data['sl_id'],
                    'sl_title' => $sl_title,
                    'is_del' => 0,
                    'add_time' => time()
                );

                $shelves_sku_info = $this->shelves_sku_info(array(
                    'sku_id' => $sku_sn,
                    'sl_id' => $data['sl_id']
                ));
                print_r($shelves_sku_info);

                if ($shelves_sku_info) {
                    if ($shelves_sku_info['is_del'] == 1) {
                        $this->db->update('shelves_sku', array(
                            'is_del' => 0
                        ), "sls_id = '{$shelves_sku_info['sls_id']}'");
                    }
                } else {
                    $this->db->insert('shelves_sku', $shelves_sku_data);
                }
            }

        }
    }

    public function shelves_sku_info($param = array())
    {
        $query = $this->db->from('shelves_sku')->where($param)->get();
        $info = $query->row_array();

        return $info;
    }

    public function shelves_info($param = array()) {
        $query = $this->db->from('shelves')->where($param)->get();
        $info = $query->row_array();

        return $info;
    }

    public function sku_number($sku_id) {
        $query = $this->db->select('sku_number')->from('goods_sku')->where("sku_id = '{$sku_id}'")->get();
        $goods_sku = $query->row_array();

        return $goods_sku['sku_number'];
    }

    public function order_occupy_sku($order_goods)
    {
        $log_info = '';
        foreach ($order_goods as $row) {

            $need_number = $row['goods_number']-$row['occupy_inventory_num'];
            $sku_number = $this->sku_number($row['sku_id']);

            if ($sku_number <= 0 || $need_number <= 0) { continue;}

            $data = array();
            if ($sku_number >= $need_number) {
                $occupy_number = $need_number;
                $data['status'] = 3;
            } else {
                $occupy_number = $sku_number;
            }

            $data['occupy_inventory_num'] = $row['occupy_inventory_num']+$occupy_number;
            $data['erp_status'] = 1;

            $this->db->update('order_goods', $data, array('rec_id' => $row['rec_id']));
            $this->take_sku($row['sku_id'], 3, 0, $occupy_number, "#{$row['order_id']} 订单取库存", $row['order_id']);

            $log_info .= "#{$row['rec_id']} 订单产品占用 <b>{$occupy_number}</b> 个库存";
        }

        return $log_info;
    }

    /**
     * 取库存
     *
     * @param $sku_id      int     SKU 编号
     * @param $quantity    int     取存多少库存
     * @param $occupy_number int   占用库存
     * @param $type        int     类型
     * @param $remark      string  备注
     * @param $source_id    int    来源ID
     * @return bool
     *
     */

    public function take_sku($sku_id, $type, $quantity, $occupy_number = 0, $remark = '', $source_id = 0)
    {

        $sku_info = $this->sku_info(array('sku_id' => $sku_id));

        if (!$sku_info) {
            return false;
        }

        if ($occupy_number > 0 ) {
            $quantity -= $occupy_number;
        }

        $data = array(
            'sku_number' => $sku_info['sku_number'] + $quantity,
            'occupy_number' => $sku_info['occupy_number'] + $occupy_number
        );

        $return_param = $this->db->update('goods_sku', $data, array('sku_id' => $sku_id));

        if ($return_param) {
            //写入记录
            $data_log = array(
                'source_id' => $source_id,
                'sku_id' => $sku_info['sku_id'],
                'storage_type' => $type,
                'ori_sku_number' => $sku_info['sku_number'],
                'deposit_number' => $quantity,
                'occupy_number' => $occupy_number,
                'log_info' => $remark,
                'admin_id' => intval($_SESSION['admin_id']),
                'add_time' => time()
            );

            $this->db->insert('goods_sku_log', $data_log);
        }

        return true;
    }

    /**
     * 采购商品加占用库存
     *
     */

    public function purchase_goods_add_number($sku_id, $deposit_number)
    {

        $sku_info = $this->sku_info(array('sku_id' => $sku_id));
        if (!$sku_info) {
            return false;
        }

        $this->db->where(array('sku_id' => $sku_id));
        $this->db->set('occupy_number', "occupy_number + {$deposit_number}", false);
        $return_param = $this->db->update('goods_sku');

        if ($return_param) {
            //写入记录
            $data_log = array(
                'sku_id' => $sku_id,
                'ori_sku_number' => $sku_info['sku_number'],
                'take_number' => $deposit_number,
                'deposit_number' => 0,
                'log_info' => '采购商品添加占用库存',
                'admin_id' => 0,
                'add_time' => time()
            );

            $this->db->insert('goods_sku_log', $data_log);
        }

    }

    /**
     *
     * 入库列表
     *
     */

    public function goods_sku_storage_list($param = array())
    {
        $this->db->select('gss.sgs_id,gss.invoice_no,gss.purchase_number,gss.storage_number,g.suppliers_id as supp_id, gs.sku_id,ss.sl_title,gs.sku_sn,gs.color,gs.size,gs.sku_number,gs.occupy_number,g.goods_id,g.goods_sn,g.original_img');
        $this->db->from('goods_sku AS gs');
        $this->db->join('shelves_sku as ss', 'ss.sku_id = gs.sku_id and ss.is_del = 0', 'left');
        $this->db->join('goods_sku_storage AS gss', 'gss.sku_id = gs.sku_id', 'left');
        $this->db->join('goods AS g', 'gs.goods_id = g.goods_id', 'left');

        if ($param['supp_id']) {
            $this->db->where("g.suppliers_id = '{$param['supp_id']}'");
        }

        if ($param['order_by']) {
            $this->db->order_by($param['order_by']);
        } else {
            $this->db->order_by('gs.sku_id DESC');
        }

        $query = $this->db->get();

        $goods_sku_supp = $query->result_array();

        return $goods_sku_supp;
    }

    public function goods_supp_list($supp_id)
    {
        $order_by = "g.goods_id DESC,gs.color ASC,FIELD(gs.size, 'S','M','L','XL','XXL','XXXL','XXXXL')";
        $goods_sku_supp = $this->goods_sku_storage_list(array('supp_id' => $supp_id, 'order_by' => $order_by));

        $goods_supp = array();
        foreach ($goods_sku_supp as $k => $sku) {

            $goods_supp[$sku['goods_id']]['original_img'] = $sku['original_img'];
            $goods_supp[$sku['goods_id']]['goods_sn'] = $sku['goods_sn'];

            $goods_supp[$sku['goods_id']]['sku'][$sku['sku_id']] = array(
                'sgs_id' => $sku['sgs_id'],
                'sku_sn' => $sku['sku_sn'],
                'sl_title' => $sku['sl_title'],
                'purchase_number' => $sku['purchase_number'],
                'storage_number' => $sku['storage_number'],
                'update_time' => $sku['update_time']
            );
        }

        return $goods_supp;
    }


    public function goods_sku_color_sale($param = array())
    {//order_time_start    order_time_end

        //$sl = "select SUM(gs.goods_number) from pcs_order_goods as og left join pcs_order_info as oi on og.order_id = oi.order_id where g.sku_id = gs.sku_id";

        if ($param['order_time_start'] && $param['order_time_end']) {
            $param['order_time_start'] = strtotime($param['order_time_start']);
            $param['order_time_end'] = strtotime($param['order_time_end']);
        }

        if ($param['web_order_by']) {

            $this->db->from('order_goods as ogi')
                ->select('SUM(ogi.goods_number) ogi_goods_number')
                ->join('order_info as oii', 'ogi.order_id = oii.order_id', 'left')
                ->where("oii.web_id = '{$param['web_order_by']}' and ogi.goods_id = g.goods_id");


            if ($param['order_time_start'] && $param['order_time_end']) {
                $this->db->where("oii.add_time >= '{$param['order_time_start']}' and oii.add_time <= '{$param['order_time_end']}'");
            }

            $sl = ' (' . $this->db->get_compiled_select() . ') DESC, ';
        }

        $this->db->from('order_goods as og')
            ->select('og.goods_id,g.goods_sn,g.original_img, SUM(og.goods_number) as or_goods_number')
            ->join('order_info as oi', 'og.order_id = oi.order_id', 'left')
           // ->join('goods_sku as gs', 'og.sku_id = gs.sku_id', 'left')
            ->join('goods as g', 'og.goods_id = g.goods_id', 'left')
            ->order_by("$sl or_goods_number desc")
            ->group_by('og.goods_id');

        if ($param['goods_name']) {
            $param['goods_name'] =  trim($param['goods_name']);
            $this->db->where("(g.goods_name like '%{$param['goods_name']}%' OR g.goods_sn like '%{$param['goods_name']}%' OR g.supp_sn like '%{$param['goods_name']}%')");
        }

        if ($param['order_time_start'] && $param['order_time_end']) {

            $this->db->where("oi.add_time >= '{$param['order_time_start']}' and oi.add_time <= '{$param['order_time_end']}'");
        }

        if ($param['siteweb'] > 0) {
            $this->db->join('shopify_goods as sg', "g.goods_id = sg.goods_id and sg.web_id = {$param['siteweb']}", 'left');
        }

        if ($param['cate_name'] > 0) {
            $this->db->where("g.cat_id = '{$param['cate_name']}'");
        }

        /*$sl = $this->db->get_compiled_select();

        $this->db->from("goods_sku as gs")
            ->select(" gs.*,g.goods_sn,g.original_img,t1.goods_number as or_goods_number")
            ->join('goods as g', 'gs.goods_id = g.goods_id', 'left')
            ->join("({$sl}) as t1", 't1.sku_id = gs.sku_id', 'left')
            ->join('shopify_goods as sg', 'g.goods_id = sg.goods_id', 'left');*/

        if ($param['goods_time_start'] && $param['goods_time_end']) {
            $param['goods_time_start'] = strtotime($param['goods_time_start']);
            $param['goods_time_end'] = strtotime($param['goods_time_end']);
            $this->db->where("g.add_time >= '{$param['goods_time_start']}' and g.add_time <= '{$param['goods_time_end']}'");
        }

        if ($param['in_time_start'] && $param['in_time_end'] && $param['siteweb'] > 0) {
            $param['in_time_start'] = strtotime($param['in_time_start']);
            $param['in_time_end'] = strtotime($param['in_time_end']);
            $this->db->where("sg.add_time >= '{$param['in_time_start']}' and sg.add_time <= '{$param['in_time_end']}' and sg.web_id = '{$param['siteweb']}'");
        }

        //$this->db->order_by("or_goods_number desc");
        //$this->db->group_by("gs.sku_id");

        $this->db->limit($this->limit_rows,$this->limit_rows * $param['page']);

        // $join_sql = $this->db->get_compiled_select();

        //$_sql = "select a.* from ({$join_sql}) a order by or_goods_number desc";

        //$query = $this->db->query($_sql);

        $query = $this->db->get();

        $_list = $query->result_array();

        $list = array();
        foreach ($_list as $k => $item) {
            $this->db->from('order_goods as og')
                ->select('oi.web_id,SUM(og.goods_number) as web_goods_number')
                ->join('order_info as oi', 'og.order_id = oi.order_id', 'left')
                ->where("og.goods_id = '{$item['goods_id']}'")
                ->group_by('oi.web_id');

            if ($param['order_time_start'] && $param['order_time_end']) {
                $this->db->where("oi.add_time >= '{$param['order_time_start']}' and oi.add_time <= '{$param['order_time_end']}'");
            }

            $query = $this->db->get();

            $_web_g_number = $query->result_array();

            foreach ($_web_g_number as $val) {
                $item['web_g_number'][$val['web_id']] = $val;
            }

            $list[$item['goods_id']] = $item;
        }

        return $list;

    }

    public function goods_sku_color_sale_count($param = array())
    {

        //$sl = "select SUM(gs.goods_number) from pcs_order_goods as og left join pcs_order_info as oi on og.order_id = oi.order_id where g.sku_id = gs.sku_id";


        $this->db->from('order_goods as og')
            ->select('count(1) as order_count')
            ->join('order_info as oi', 'og.order_id = oi.order_id', 'left')
            //->join('goods_sku as gs', 'og.sku_id = gs.sku_id', 'left')
            ->join('goods as g', 'og.goods_id = g.goods_id', 'left')
            ->group_by('og.goods_id');

        if ($param['order_time_start'] && $param['order_time_end']) {

            $param['order_time_start'] = strtotime($param['order_time_start']);
            $param['order_time_end'] = strtotime($param['order_time_end']);

            $this->db->where("oi.add_time >= '{$param['order_time_start']}' and oi.add_time <= '{$param['order_time_end']}'");
        }

        if ($param['siteweb'] > 0) {
            $this->db->join('shopify_goods as sg', "g.goods_id = sg.goods_id and sg.web_id = {$param['siteweb']}", 'left');
        }

        /*$sl = $this->db->get_compiled_select();

        $this->db->from("goods_sku as gs")
            ->select(" gs.*,g.goods_sn,g.original_img,t1.goods_number as or_goods_number")
            ->join('goods as g', 'gs.goods_id = g.goods_id', 'left')
            ->join("({$sl}) as t1", 't1.sku_id = gs.sku_id', 'left')
            ->join('shopify_goods as sg', 'g.goods_id = sg.goods_id', 'left');*/

        if ($param['goods_time_start'] && $param['goods_time_end']) {
            $param['goods_time_start'] = strtotime($param['goods_time_start']);
            $param['goods_time_end'] = strtotime($param['goods_time_end']);
            $this->db->where("g.add_time >= '{$param['goods_time_start']}' and g.add_time <= '{$param['goods_time_end']}'");
        }

        if ($param['in_time_start'] && $param['in_time_end'] && $param['siteweb'] > 0) {
            $param['in_time_start'] = strtotime($param['in_time_start']);
            $param['in_time_end'] = strtotime($param['in_time_end']);
            $this->db->where("sg.add_time >= '{$param['in_time_start']}' and sg.add_time <= '{$param['in_time_end']}' and sg.web_id = '{$param['siteweb']}'");
        }

        //$this->db->order_by("or_goods_number desc");
        //$this->db->group_by("gs.sku_id");

        $this->db->limit($this->limit_rows,$this->limit_rows * $param['page']);

        // $join_sql = $this->db->get_compiled_select();

        //$_sql = "select a.* from ({$join_sql}) a order by or_goods_number desc";

        //$query = $this->db->query($_sql);

        $query = $this->db->get();

        $_list = $query->result_array();

        $list = array();
        foreach ($_list as $k => $item) {
            $this->db->from('order_goods as og')
                ->select('oi.web_id,SUM(og.goods_number) as web_goods_number')
                ->join('order_info as oi', 'og.order_id = oi.order_id', 'left')
                ->where("og.goods_id = '{$item['goods_id']}'")
                ->group_by('oi.web_id');

            if ($param['order_time_start'] && $param['order_time_end']) {
                $this->db->where("oi.add_time >= '{$param['order_time_start']}' and oi.add_time <= '{$param['order_time_end']}'");
            }

            $query = $this->db->get();

            $_web_g_number = $query->result_array();

            foreach ($_web_g_number as $val) {
                $item['web_g_number'][$val['web_id']] = $val;
            }

            $list[$item['sku_id']] = $item;
        }

        return $list;

    }

    public function e_goods_list($goods_id = 0)
    {
        $query = $this->db->from('goods_sku as gs')
            ->select('gs.*,g.goods_sn,g.cn_name,g.goods_name,g.goods_weight,g.shopify_id,g.original_img,g.supp_url,g.in_price,g.shop_price,s.suppliers_name,gt.cat_name,gt.cat_name_zh')
            ->join('goods as g', 'gs.goods_id = g.goods_id', 'left')
            ->join('suppliers as s', 'g.suppliers_id = s.suppliers_id', 'left')
            ->join('goods_type gt', 'g.type_id = gt.cat_id', 'left');

        if ($goods_id) {
            $this->db->where("g.goods_id = {$goods_id}");
        } else {
            $this->db->where('gs.sku_id > 30000');
        }

        $this->db->order_by('gs.sku_id ASC');

        $query = $this->db->get();

        $list = $query->result_array();


        $this->db->update('goods', array(
            'is_puyuan' => 1
        ), "is_puyuan = 0");


        return $list;
    }

    /**
     * 获取采购入库表信息
     *
     */

    public function goods_sku_storage_info($param)
    {
        $this->db->from('goods_sku_storage');
        $this->db->where($param);
        $query = $this->db->get();

        $info = $query->row_array();

        if (!$info) {
            $this->db->insert('goods_sku_storage', array(
                'supp_id' => $param['supp_id'],
                'sku_id' => $param['sku_id'],
                'invoice_no' => '',
                'purchase_number' => 0,
                'storage_number' => 0
            ));

            $id = $this->db->insert_id();

            $this->db->from('goods_sku_storage');
            $this->db->where(array('sgs_id' => $id));
            $_query = $this->db->get();

            $info = $_query->row_array();
        }

        return $info;
    }


    /**
     * 根据订单减去占用库存
     *
     */

    public function subtract_order_goods($order_id)
    {
        $query = $this->db->from('order_goods')->where('order_id', $order_id)->get();

        $order_goods = $query->result_array();

        foreach ($order_goods as $row) {
            $this->goods_sku_model->take_sku($row['sku_id'], 3, 0, -$row['occupy_inventory_num'], "#{$row['order_id']} 订单产品减去占用库存", $row['order_id']);
        }

        return true;
    }

        //E站列表查询
    public function goods_sku_up($goods_id){
        $query = $this->db->from('goods')
            ->where('goods_id', $goods_id)
            ->select('original_img')
            ->get();
        $original_img = $query->row_array();

        $query = $this->db->from('goods_sku')
            ->where('goods_id', $goods_id)
            ->select('sku_id,sku_sn,shopify_id,is_on_sale')
            ->get();
        $goods_sku = $query->result_array();
        foreach ($goods_sku as $key => $value) {
            $goods_sku[$key]['original_img'] = $original_img['original_img'];
        }
        return $goods_sku;
    }
    //查询下架商品sku
    public function sku_select($id){
        //查询数据是上架含是下架
        $query = $this->db->from('goods_sku')
            ->where('sku_id', $id)
            ->select('sku_sn,goods_id,is_on_sale')
            ->get();
        $arr = $query->row_array();
        if($arr['is_on_sale'] == 1){
            //把sku下架
            $this->db->where('sku_id',$id);
            $this->db->update('goods_sku',['is_on_sale'=>0]);
            //下架之后查看这个商品含有没有sku寻在
            $query = $this->db->from('goods_sku')
            ->where(['goods_id'=>$arr['goods_id'],'is_on_sale'=>1])
            ->get();
            $is_sku = $query->row_array();
            if(empty($is_sku)){
                //下架商品
                $this->db->where('goods_id',$arr['goods_id']);
                $this->db->update('goods',['is_on_sale'=>0]);
                $this->db->where('goods_id',$arr['goods_id']);
                $this->db->update('shopify_goods',['is_sales'=>0]);
                return ['res'=>2,'data'=>'把产品和sku下架','sku_sn'=>$arr['sku_sn']];
            }else{
                return ['res'=>2,'data'=>'只下架sku','sku_sn'=>$arr['sku_sn']];
            }
        }else{
            //把sku上架
            $this->db->where('sku_id',$id);
            $this->db->update('goods_sku',['is_on_sale'=>1]);
            //查询商品是否下架
            $query = $this->db->from('goods')
            ->where(['goods_id'=>$arr['goods_id']])
            ->select('is_on_sale')
            ->get();
            $goods = $query->row_array();
            //把商品上架
            if($goods['is_on_sale'] == 0){

                $this->db->where('goods_id',$arr['goods_id']);
                $this->db->update('goods',['is_on_sale'=>1]);
                $this->db->where('goods_id',$arr['goods_id']);
                $this->db->update('shopify_goods',['is_sales'=>1]);
                return ['res'=>1,'data'=>'上架产品和sku','sku_sn'=>$arr['sku_sn']];
            }else{
                return ['res'=>1,'data'=>'只上架sku','sku_sn'=>$arr['sku_sn']];
            }
            
        }
    }

    public function update($sku_ids, $data = array())
    {
        $this->db->where_in('sku_id',$sku_ids);

        if (isset($data['is_on_sale'])) {
            $this->db->set('is_on_sale', $data['is_on_sale']);
            $this->db->set('on_sale_time', time());
        }

        $this->db->update('goods_sku');
    }

    public function salse_update($data, $sku_sn)
    {

        $this->db->where('sku_sn', $sku_sn);

        $this->db->set('is_on_sale', $data['is_on_sale']);
        $this->db->set('on_sale_time', $data['on_sale_time']);

        $this->db->update('goods_sku');
    }

    //下架goods
    public function up_goods($goods_id){
        //把sku下架
        $this->db->where('goods_id',$goods_id);
        $this->db->update('goods_sku',['is_on_sale'=>0]);
        //下架商品
        $this->db->where('goods_id',$goods_id);
        $this->db->update('goods',['is_on_sale'=>0]);
        $this->db->where('goods_id',$goods_id);
        $this->db->update('shopify_goods',['is_sales'=>0]);
    }

    //查询商品所有sku
    public function goods_sku_select($goods_id){
        $query = $this->db->from('goods_sku')
            ->where('goods_id', $goods_id)
            ->select('sku_sn,sku_id,color,size,is_on_sale')
            ->get();
        $_goods_sku = $query->result_array();

        $goods_sku = array();
        foreach ($_goods_sku as $sku) {
            $goods_sku[$sku['sku_id']] = $sku;
        }

        return $goods_sku;
    }

    //上下架SKU操作日志
    public function shelf_log($arr){

        $_arr = array(
            'goods_id' => $arr['goods_id'],
            'sku_id' => $arr['sku_id'],
            'admin_id' => intval($_SESSION['admin_id']),
            'reason' => intval($arr['reason']),
            'is_type' => $arr['is_type'],
            'add_time' => time()
        );

        $this->db->insert('shelf_log', $_arr);
    }

    //下架sku查询
    public function lower_shelf_sku($param){
        //6天前 + 今天
        $time = time() - 518400;
        if($param['lower'] == 1){
            $this->db->where('gs.add_time >',$time);
        }
        if($param['lower'] == 2){
            $this->db->where('gs.add_time <',$time);
        }

        if($param['supp_id'] >= 1){
            $this->db->where(['g.suppliers_id' => $param['supp_id']]);
        }

        if($param['add_time_start'] && $param['add_time_end']){
            $start = strtotime($param['add_time_start']);
            $end   = strtotime($param['add_time_end']);
            $this->db->where("gs.update_time between $start and $end");
        }
            
            
            
        $this->db->select('gs.sku_id,gs.sku_sn,g.original_img,g.goods_name,gs.shop_price,gs.add_time,gs.color,gs.size');
        $this->db->from('goods_sku AS gs');
        $this->db->join('goods as g', 'gs.goods_id = g.goods_id', 'left');
        $this->db->limit($this->limit_rows,$this->limit_rows * $param['page']);
        $this->db->order_by('gs.sku_id desc');
        $this->db->where(['gs.is_on_sale'=>0]);
        $query = $this->db->get();
        $lower_sku = $query->result_array();
        // print_R($arr);die;


        // $lower_sku = $this->db->from('goods_sku')
        //     ->limit($this->limit_rows,$this->limit_rows * $param['page'])
        //     ->order_by('sku_id desc')
        //     ->where(['is_on_sale'=>0])
        //     ->get()
        //     ->result_array();
        foreach ($lower_sku as $k => $v) {
            $reason = $this->db->from('shelf_log')->where(['sku_id'=>$v['sku_id']])->order_by('id desc')->select('reason')->get()->row_array();
            $lower_sku[$k]['reason'] = $reason['reason'];
            if($v['add_time'] > $time){

                $lower_sku[$k]['is_type'] = '新品';
            }else{
                $lower_sku[$k]['is_type'] = '老品';
            }
        }
        return $lower_sku;
    }

    //分页
    public function lower_shelf_sku_num($param){
        //6天前 + 今天
        $time = time() - 518400;
        if($param['lower'] == 1){
            $this->db->where('gs.add_time >',$time);
        }
        if($param['lower'] == 2){
            $this->db->where('gs.add_time <',$time);
        }

        if($param['supp_id'] >= 1){
            $this->db->where(['g.suppliers_id' => $param['supp_id']]);
        }
        if($param['add_time_start'] && $param['add_time_end']){
            $start = strtotime($param['add_time_start']);
            $end   = strtotime($param['add_time_end']);
            $this->db->where("gs.update_time between $start and $end");
        }

        $this->db->from('goods_sku AS gs');
        $this->db->join('goods as g', 'gs.goods_id = g.goods_id', 'left');
        $this->db->order_by('gs.sku_id desc');
        $this->db->where(['gs.is_on_sale'=>0]);

        return $this->db->count_all_results();
    }

    public function lowershelf($start,$end){
        // print_R($start);die;
        // if($start && $end){
        //     $start = strtotime($start);
        //     $end   = strtotime($end);
        // }else{
        //     $start = strtotime(date('Y-m-d'));
        //     $end   = strtotime(date('Y-m-d')) + 86400;
        // }
        
        $counts = $this->db->from('goods_sku')
            ->select('count(*) as count')
            ->where(['is_on_sale'=>0])
            ->where("update_time between $start and $end")
            ->get()->row_array();

        return $counts;
    }

}
